using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;

namespace SqlScriptCreator.Generators
{
    /// <summary>
    /// Common static methods that provide SQL Server specific data transformation and checks.
    /// These methods are used within the script generators.
    /// </summary>
    class SqlTools
    {
        static CultureInfo sqlCultureInfo = new CultureInfo("en-US", false);
        /// <summary>
        /// Return a data value as string representation and provide the correct quotation
        /// according to the datatype. VARCHAR and CHAR fields are quoted with single quotes.
        /// </summary>
        /// <param name="data">A data value from a table cell as string.</param>
        /// <param name="dataType">The SQL datatype of the value as string. e.g. VARCHAR, INT, etc.</param>
        /// <returns>The quoted representation of the data value, e.g. 'Text'</returns>
        public static string getSqlDataRepresentation(object data, string dataType)
        {
            if (Convert.IsDBNull(data))
                return "NULL";

            if (dataType.ToLower() == "datetime")
                return "CONVERT(DATETIME,'" + data.ToString() + "')";
            if (dataType.ToLower() == "float" || dataType.ToLower() == "decimal"  || dataType.ToLower() == "money")
                return Convert.ToDouble(data).ToString(sqlCultureInfo.NumberFormat);
            if (dataType.ToLower() == "varchar" || dataType.ToLower() == "char")
                return "'" + data.ToString() + "'";
            if (dataType.ToLower() == "nvarchar" || dataType.ToLower() == "nchar")
                return "N'" + data.ToString() + "'";
            if (dataType.ToLower() == "bit")
                if (Convert.ToBoolean(data)) return "1";
                else return "0";
            return data.ToString();
        }

        /// <summary>
        /// Determines wether an SQL datatype can be used as a locale variable. SQL Server prohibts
        /// the use of TEXT, NTEXT and IMAGE datatypes a variables so this function is used to
        /// treat these datatypes correctly within the scropt generators.
        /// </summary>
        /// <param name="dataType">The SQL datatype of the value as string. e.g. VARCHAR, INT, etc.</param>
        /// <returns>A bool which defines if the datatype can be used as variable.</returns>
        public static bool isValidForLocalVariable(string dataType)
        {
            // Meldung 2739, Ebene 16, Status 1, Zeile 1
            // The text, ntext, and image data types are invalid for local variables. 
            if (dataType.ToLower() == "text" || dataType.ToLower() == "ntext" || dataType.ToLower() == "image")
                return false;
            else return true;
        }

        public static string getDatatypeExpression(string datatype, int size)
        {
            string lengthSpecification = "";
            if (datatype.ToLower() == "varchar" || datatype.ToLower() == "char" ||
               datatype.ToLower() == "nvarchar" || datatype.ToLower() == "nchar")
               lengthSpecification = "(" + size.ToString() + ")";

           return datatype.ToUpper() + lengthSpecification;
        }

    }

}
